package com.github.wxhnyfy.service.impl;

import com.github.wxhnyfy.ExcelData;
import com.github.wxhnyfy.NoHeaderSheetData;
import com.github.wxhnyfy.SheetData;
import com.github.wxhnyfy.service.ExcelToolService;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.IOUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * Apache POI Excel工具类
 *
 * @author chenwc
 */
public class ApachePoiExcelTool implements ExcelToolService {

    /**
     * byte数组最大值
     */
    public static final Integer ByteArrayMaxOverride = 300000000;
    /**
     * 文件最大值， 10M
     */
    public static final Integer MaxFileSize = 10 * 1024 * 1024;
    private static final Logger logger = LoggerFactory.getLogger(ApachePoiExcelTool.class);

    /**
     * 获取单元格的值
     *
     * @param cellData 单元格数据
     * @return 值
     */
    public static String getCellValue(Cell cellData) {
        CellType cellType = cellData.getCellType();
        String cellValue = "";
        switch (cellType) {
            //字符串
            case STRING:
                cellValue = cellData.getStringCellValue();
                break;
            //布尔
            case BOOLEAN:
                cellValue = String.valueOf(cellData.getBooleanCellValue());
                break;
            case _NONE:
                //空
            case BLANK:
                //错误
            case ERROR:
                cellValue = null;
                break;
            //数字（日期、普通数字）
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cellData)) {
                    //如果是日期
                    Date date = cellData.getDateCellValue();
                    cellValue = new DateTime(date).toString("yyyy-MM-dd HH:mm:ss");
                } else {
                    //不是日期格式，防止数字过长，转为字符串
                    double value = cellData.getNumericCellValue();
                    cellValue = convertDouble(value);
                }
                break;
            //公式
            case FORMULA:
                cellValue = cellData.toString();
                break;
        }
        return cellValue;
    }

    /**
     * 转换 double 为字符串
     *
     * @param value double值
     * @return 转换后的字符串
     */
    public static String convertDouble(double value) {
        // 判断是否是整数
        if (value == Math.floor(value)) {
            // 是整数，转换为int再转换为字符串
            int intValue = (int) value;
            return Integer.toString(intValue);
        } else {
            // 不是整数，直接转换为字符串
            return Double.toString(value);
        }
    }

    /**
     * 获取Sheet数据
     *
     * @param sheet sheet
     * @return sheet数据
     */
    private SheetData getSheetData(Sheet sheet) {
        SheetData sheetData = new SheetData();
        //获取表头
        Row row = sheet.getRow(0);
        if (row != null) {
            //获取所有的列
            int cells = row.getPhysicalNumberOfCells();
            List<String> header = new ArrayList<>();
            for (int col = 0; col < cells; col++) {
                //获取当前列
                Cell cell = row.getCell(col);
                if (cell != null) {
                    //获取当前行的第 col 列的值
                    header.add(cell.getStringCellValue());
                }
            }
            sheetData.setHeader(header);
        }
        //获取有多少行
        int rowCount = sheet.getPhysicalNumberOfRows();
        //从1开始，第一行是标题
        List<Map<String, String>> rowDataList = new ArrayList<>();
        for (int rowNum = 1; rowNum < rowCount; rowNum++) {
            //获取行数据
            Row rowData = sheet.getRow(rowNum);
            if (rowData != null) {
                Map<String, String> cellDataMap = new HashMap<>();
                List<String> header = sheetData.getHeader();
                for (int col = 0; col < header.size(); col++) {
                    //获取当前列的值
                    Cell cellData = rowData.getCell(col);
                    //匹配列的类型
                    if (cellData != null) {
                        //获取列的类型
                        String cellValue = getCellValue(cellData);
                        cellDataMap.put(header.get(col), cellValue);
                    } else {
                        cellDataMap.put(header.get(col), null);
                    }
                }
                rowDataList.add(cellDataMap);
            }
        }
        sheetData.setSheetName(sheet.getSheetName());
        sheetData.setDataMapList(rowDataList);
        return sheetData;
    }

    /**
     * 读取Excel文件第 1 个Sheet
     *
     * @param excelFile Excel文件
     * @return sheet数据
     */
    @Override
    public SheetData read(File excelFile) {
        return read(excelFile, 0);
    }

    /**
     * 读取整个Excel文件
     *
     * @param excelFile Excel文件
     * @return Excel数据
     */
    @Override
    public ExcelData readExcelFile(File excelFile) {
        //获取文件流
        FileInputStream fis = null;
        Workbook workbook;
        ExcelData excelData = new ExcelData();
        long start = 0L;
        try {
            if (!excelFile.exists()) {
                throw new RuntimeException("Excel文件: " + excelFile.getName() + "不存在！");
            }
            logger.info("读取文件：{}", excelFile.getAbsolutePath());
            start = System.currentTimeMillis();
            fis = new FileInputStream(excelFile);
            if (excelFile.length() > MaxFileSize) {
                IOUtils.setByteArrayMaxOverride(ByteArrayMaxOverride);
            }
            //获取一个工作簿
            if (excelFile.getName().toLowerCase().endsWith("xlsx")) {
                workbook = new XSSFWorkbook(fis);
            } else {
                workbook = new HSSFWorkbook(fis);
            }
            Iterator<Sheet> sheetIterator = workbook.sheetIterator();
            AtomicInteger cnt = new AtomicInteger();
            Map<Integer, String> sheetNameMap = new HashMap<>();
            List<SheetData> sheetDataList = new ArrayList<>();
            while (sheetIterator.hasNext()) {
                Sheet sheet = sheetIterator.next();
                int sheetIndex = cnt.getAndIncrement();
                sheetNameMap.put(sheetIndex, sheet.getSheetName());
                SheetData sheetData = getSheetData(sheet);
                sheetDataList.add(sheetData);
            }
            excelData.setSheetSize(cnt.get());
            excelData.setSheetNameMap(sheetNameMap);
            excelData.setSheetDataList(sheetDataList);
            excelData.setFileName(excelFile.getName());
        } catch (IOException e) {
            logger.error("Error occurred: ", e);
        } finally {
            try {
                if (null != fis) {
                    fis.close();
                }
            } catch (Exception e) {
                logger.error("Error occurred: ", e);
            }
        }
        long end = System.currentTimeMillis();
        logger.info("读取文件完成，总耗时：{} 秒", (end - start) / 1000.0);
        return excelData;
    }

    /**
     * 指定Sheet名称读取Excel文件
     *
     * @param excelFile Excel文件
     * @param sheetName Sheet名称
     * @return sheet数据
     */
    @Override
    public SheetData read(File excelFile, String sheetName) {
        //获取文件流
        FileInputStream fis = null;
        Workbook workbook;
        SheetData sheetData = new SheetData();
        long start = 0L;
        try {
            if (!excelFile.exists()) {
                throw new RuntimeException("Excel文件: " + excelFile.getName() + "不存在！");
            }
            logger.info("读取文件：{}", excelFile.getAbsolutePath());
            start = System.currentTimeMillis();
            fis = new FileInputStream(excelFile);
            if (excelFile.length() > MaxFileSize) {
                IOUtils.setByteArrayMaxOverride(ByteArrayMaxOverride);
            }
            //获取一个工作簿
            if (excelFile.getName().toLowerCase().endsWith("xlsx")) {
                workbook = new XSSFWorkbook(fis);
            } else {
                workbook = new HSSFWorkbook(fis);
            }
            //获取一个工作表
            Sheet sheet = workbook.getSheet(sheetName);
            sheetData = getSheetData(sheet);
        } catch (IOException e) {
            logger.error("Error occurred: ", e);
        } finally {
            try {
                if (null != fis) {
                    fis.close();
                }
            } catch (Exception e) {
                logger.error("Error occurred: ", e);
            }
        }
        long end = System.currentTimeMillis();
        logger.info("读取文件完成，总耗时：{} 秒", (end - start) / 1000.0);
        return sheetData;
    }

    /**
     * 读取Excel文件第 index 个Sheet
     *
     * @param excelFile  Excel文件
     * @param sheetIndex Sheet下标
     * @return sheet数据
     */
    @Override
    public SheetData read(File excelFile, int sheetIndex) {
        //获取文件流
        FileInputStream fis = null;
        Workbook workbook;
        SheetData sheetData = new SheetData();
        long start = 0L;
        try {
            if (!excelFile.exists()) {
                throw new RuntimeException("Excel文件: " + excelFile.getName() + "不存在！");
            }
            logger.info("读取文件：{}", excelFile.getAbsolutePath());
            start = System.currentTimeMillis();
            fis = new FileInputStream(excelFile);
            if (excelFile.length() > MaxFileSize) {
                IOUtils.setByteArrayMaxOverride(ByteArrayMaxOverride);
            }
            //获取一个工作簿
            if (excelFile.getName().toLowerCase().endsWith("xlsx")) {
                workbook = new XSSFWorkbook(fis);
            } else {
                workbook = new HSSFWorkbook(fis);
            }
            //获取一个工作表
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            sheetData = getSheetData(sheet);
        } catch (IOException e) {
            logger.error("Error occurred: ", e);
        } finally {
            try {
                if (null != fis) {
                    fis.close();
                }
            } catch (Exception e) {
                logger.error("Error occurred: ", e);
            }
        }
        long end = System.currentTimeMillis();
        logger.info("读取文件完成，总耗时：{} 秒", (end - start) / 1000.0);
        return sheetData;
    }

    /**
     * 读取excel文件，没有表头的sheet
     *
     * @param excelFile  excel文件
     * @param sheetIndex 要读取的sheet页签下标
     * @return excel文件数据
     */
    @Override
    public SheetData readByNoHeaderSheet(File excelFile, int sheetIndex) {
        //获取文件流
        FileInputStream fis = null;
        Workbook workbook;
        SheetData sheetData = new SheetData();
        long start = 0L;
        try {
            if (!excelFile.exists()) {
                throw new RuntimeException("Excel文件: " + excelFile.getName() + "不存在！");
            }
            logger.info("读取文件：{}", excelFile.getAbsolutePath());
            start = System.currentTimeMillis();
            fis = new FileInputStream(excelFile);
            if (excelFile.length() > MaxFileSize) {
                IOUtils.setByteArrayMaxOverride(ByteArrayMaxOverride);
            }
            //获取一个工作簿
            if (excelFile.getName().toLowerCase().endsWith("xlsx")) {
                workbook = new XSSFWorkbook(fis);
            } else {
                workbook = new HSSFWorkbook(fis);
            }
            //获取一个工作表
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            //获取有多少行
            int rowCount = sheet.getPhysicalNumberOfRows();
            //从0开始
            List<Map<String, String>> rowDataList = new ArrayList<>();

            int colCount = sheet.getRow(0).getPhysicalNumberOfCells();
            List<String> header = new ArrayList<>();
            for (int col = 0; col < colCount; col++) {
                header.add(String.valueOf(col));
            }
            sheetData.setHeader(header);

            for (int rowNum = 0; rowNum < rowCount; rowNum++) {
                //获取行数据
                Row rowData = sheet.getRow(rowNum);
                if (rowData != null) {
                    Map<String, String> cellDataMap = new HashMap<>();
                    for (int col = 0; col < colCount; col++) {
                        //获取当前列的值
                        Cell cellData = rowData.getCell(col);
                        //匹配列的类型
                        if (cellData != null) {
                            //获取列的类型
                            String cellValue = getCellValue(cellData);
                            cellDataMap.put(String.valueOf(col), cellValue);
                        } else {
                            cellDataMap.put(String.valueOf(col), null);
                        }
                    }
                    rowDataList.add(cellDataMap);
                }
            }

            sheetData.setSheetName(sheet.getSheetName());
            sheetData.setDataMapList(rowDataList);
        } catch (IOException e) {
            logger.error("Error occurred: ", e);
        } finally {
            try {
                if (null != fis) {
                    fis.close();
                }
            } catch (Exception e) {
                logger.error("Error occurred: ", e);
            }
        }
        long end = System.currentTimeMillis();
        logger.info("读取文件完成，总耗时：{} 秒", (end - start) / 1000.0);
        return sheetData;
    }

    /**
     * 读取excel文件，没有表头的sheet
     *
     * @param excelFile  excel文件
     * @param sheetIndex 要读取的sheet页签下标
     * @return excel文件数据
     */
    @Override
    public NoHeaderSheetData readByNoHeaderSheetData(File excelFile, int sheetIndex) {
        //获取文件流
        FileInputStream fis = null;
        Workbook workbook;
        NoHeaderSheetData sheetData = new NoHeaderSheetData();
        long start = 0L;
        try {
            if (!excelFile.exists()) {
                throw new RuntimeException("Excel文件: " + excelFile.getName() + "不存在！");
            }
            logger.info("读取文件：{}", excelFile.getAbsolutePath());
            start = System.currentTimeMillis();
            fis = new FileInputStream(excelFile);
            if (excelFile.length() > MaxFileSize) {
                IOUtils.setByteArrayMaxOverride(ByteArrayMaxOverride);
            }
            //获取一个工作簿
            if (excelFile.getName().toLowerCase().endsWith("xlsx")) {
                workbook = new XSSFWorkbook(fis);
            } else {
                workbook = new HSSFWorkbook(fis);
            }
            //获取一个工作表
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            //获取有多少行
            int rowCount = sheet.getPhysicalNumberOfRows();
            //从0开始
            List<List<String>> rowDataList = new ArrayList<>();
            for (int rowNum = 0; rowNum < rowCount; rowNum++) {
                //获取行数据
                Row rowData = sheet.getRow(rowNum);
                if (rowData != null) {
                    int colCount = rowData.getPhysicalNumberOfCells();
                    List<String> cellDataList = new ArrayList<>();
                    for (int col = 0; col < colCount; col++) {
                        //获取当前列的值
                        Cell cellData = rowData.getCell(col);
                        //匹配列的类型
                        if (cellData != null) {
                            //获取列的类型
                            String cellValue = getCellValue(cellData);
                            cellDataList.add(cellValue);
                        } else {
                            cellDataList.add(null);
                        }
                    }
                    rowDataList.add(cellDataList);
                }
            }
            sheetData.setSheetName(sheet.getSheetName());
            sheetData.setDataList(rowDataList);
        } catch (IOException e) {
            logger.error("Error occurred: ", e);
        } finally {
            try {
                if (null != fis) {
                    fis.close();
                }
            } catch (Exception e) {
                logger.error("Error occurred: ", e);
            }
        }
        long end = System.currentTimeMillis();
        logger.info("读取文件完成，总耗时：{} 秒", (end - start) / 1000.0);
        return sheetData;
    }

    /**
     * 自定义读取excel文件的sheet
     *
     * @param excelFile   excel文件
     * @param sheetIndex  要读取的sheet页签下标（从0开始）
     * @param startRow    开始读取的行号（从0开始）
     * @param endRow      结束读取的行号（从0开始）
     * @param startColumn 开始读取的列数（从0开始）
     * @param endColumn   结束读取的总列数（从0开始）
     * @return excel文件数据
     */
    @Override
    public SheetData read(File excelFile, int sheetIndex, int startRow, int endRow, int startColumn, int endColumn) {
        //获取文件流
        FileInputStream fis = null;
        Workbook workbook;
        SheetData sheetData = new SheetData();
        long start = 0L;
        try {
            if (!excelFile.exists()) {
                throw new RuntimeException("Excel文件: " + excelFile.getName() + "不存在！");
            }
            logger.info("读取文件：{}", excelFile.getAbsolutePath());
            start = System.currentTimeMillis();
            fis = new FileInputStream(excelFile);
            if (excelFile.length() > MaxFileSize) {
                IOUtils.setByteArrayMaxOverride(ByteArrayMaxOverride);
            }
            //获取一个工作簿
            if (excelFile.getName().toLowerCase().endsWith("xlsx")) {
                workbook = new XSSFWorkbook(fis);
            } else {
                workbook = new HSSFWorkbook(fis);
            }
            //获取一个工作表
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            List<String> header = new ArrayList<>();
            for (int col = startColumn; col <= endColumn; col++) {
                header.add(String.valueOf(col));
            }
            sheetData.setHeader(header);
            if (endRow >= sheet.getPhysicalNumberOfRows()) {
                endRow = sheet.getPhysicalNumberOfRows() - 1;
            }
            //从0开始
            List<Map<String, String>> rowDataList = new ArrayList<>();
            for (int rowNum = startRow; rowNum < endRow; rowNum++) {
                //获取行数据
                Row rowData = sheet.getRow(rowNum);
                if (rowData != null) {
                    Map<String, String> cellDataMap = new HashMap<>();
                    for (int col = 0, k = startColumn; col < header.size(); col++, k++) {
                        //获取当前列的值
                        Cell cellData = rowData.getCell(k);
                        //匹配列的类型
                        if (cellData != null) {
                            //获取列的类型
                            String cellValue = getCellValue(cellData);
                            cellDataMap.put(header.get(col), cellValue);
                        } else {
                            cellDataMap.put(header.get(col), null);
                        }
                    }
                    rowDataList.add(cellDataMap);
                }
            }
            sheetData.setSheetName(sheet.getSheetName());
            sheetData.setDataMapList(rowDataList);
        } catch (IOException e) {
            logger.error("Error occurred: ", e);
        } finally {
            try {
                if (null != fis) {
                    fis.close();
                }
            } catch (Exception e) {
                logger.error("Error occurred: ", e);
            }
        }
        long end = System.currentTimeMillis();
        logger.info("读取文件完成，总耗时：{} 秒", (end - start) / 1000.0);
        return sheetData;
    }

    /**
     * 写入Excel文件
     *
     * @param excelFile Excel文件
     * @param sheetData Sheet数据
     */
    @Override
    public void write(File excelFile, SheetData sheetData) {
        long start = System.currentTimeMillis();
        if (!excelFile.exists()) {
            try {
                FileUtils.touch(excelFile);
            } catch (IOException e) {
                logger.error("Error occurred: ", e);
            }
        }
        logger.info("写入文件：{}", excelFile.getAbsolutePath());
        if (!excelFile.getName().toLowerCase().endsWith(".xlsx")) {
            logger.info("只支持 .xlsx 的文件类型写入！");
            return;
        }
        if (StringUtils.isEmpty(sheetData.getSheetName())) {
            sheetData.setSheetName("Sheet1");
        }
        // 创建一个工作簿 07
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 创建一个工作表
        XSSFSheet sheet = workbook.createSheet(sheetData.getSheetName());
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        //设置字体
        font.setFontName("宋体");
        //设置字号
        font.setFontHeightInPoints((short) 12);
        //加粗
        font.setBold(true);
        //设置字体颜色
        font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
        cellStyle.setFont(font);
        //文本自动换行
        cellStyle.setWrapText(true);
        //左右居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //上下居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        List<String> header = sheetData.getHeader();
        // 创建第一行
        XSSFRow row1 = sheet.createRow(0);
        for (int i = 0; i < header.size(); i++) {
            // 创建一个单元格
            XSSFCell cell = row1.createCell(i);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(header.get(i));
        }
        logger.info("写入标题行完成");

        // 调整列宽
        for (int colNum = 0; colNum < header.size(); colNum++) {
            //固定列宽
            sheet.setColumnWidth(colNum, 20 * 256);
        }
        logger.info("设置列宽完成");

        List<Map<String, String>> sheetDataList = sheetData.getDataMapList();
        for (int i = 0; i < sheetDataList.size(); i++) {
            // 创建第二行
            XSSFRow row2 = sheet.createRow(i + 1);
            Map<String, String> rowDataMap = sheetDataList.get(i);
            for (int j = 0; j < header.size(); j++) {
                // 创建一个单元格
                XSSFCell cell = row2.createCell(j);
                if (null == rowDataMap.get(header.get(j))) {
                    cell.setCellValue("");
                } else {
                    cell.setCellValue(rowDataMap.get(header.get(j)));
                }
            }
        }
        logger.info("写入数据行完成");

        //第一行开启筛选
        sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, header.size() - 1));
        logger.info("开启标题行筛选完成");
        /*
            四个参数分别代表:
            cellNum:表示要冻结的列数；
            rowNum:表示要冻结的行数；
            firstCellNum:表示被固定列右边第一列的列号；
            firstRollNum :表示被固定行下边第一列的行号;
            注意: 后2个参数均从0开始计算列号和行号,且firstCellNum>=cellNum &&firstRollNum >=cellNum
            sheet.createFreezePane(1,0,1,0);//就是固定了首列,列号的显示为:A,BCDEF...
            sheet.createFreezePane(1,0,3,0);//固定了首列,列号的显示为:A,DEF...
            //注意:BC列不是被隐藏,而是默认显示列为A,DEF,若想要看BC列,只需移动滚轮即可.行号同理
         */
        //这里是只冻结第一行
        sheet.createFreezePane(0, 1, 0, 1);
        logger.info("冻结标题行完成");

        FileOutputStream fileOutputStream = null;
        try {
            // 创建文件
            fileOutputStream = new FileOutputStream(excelFile);
            // 写入文件
            workbook.write(fileOutputStream);
        } catch (IOException e) {
            logger.error("Error occurred: ", e);
        } finally {
            try {
                if (null != fileOutputStream) {
                    fileOutputStream.close();
                }
            } catch (IOException e) {
                logger.error("Error occurred: ", e);
            }
        }
        long end = System.currentTimeMillis();
        logger.info("写入文件完成，总耗时：{} 秒", (end - start) / 1000.0);
    }

    /**
     * 写入Excel文件
     *
     * @param excelFile Excel文件
     * @param sheetData Sheet数据
     */
    @Override
    public void writeNoHeaderSheet(File excelFile, NoHeaderSheetData sheetData) {
        long start = System.currentTimeMillis();
        if (!excelFile.exists()) {
            try {
                FileUtils.touch(excelFile);
            } catch (IOException e) {
                logger.error("Error occurred: ", e);
            }
        }
        logger.info("写入文件：{}", excelFile.getAbsolutePath());
        if (!excelFile.getName().toLowerCase().endsWith(".xlsx")) {
            logger.info("只支持 .xlsx 的文件类型写入！");
            return;
        }
        if (StringUtils.isEmpty(sheetData.getSheetName())) {
            sheetData.setSheetName("Sheet1");
        }
        // 创建一个工作簿 07
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 创建一个工作表
        XSSFSheet sheet = workbook.createSheet(sheetData.getSheetName());

        List<List<String>> dataList = sheetData.getDataList();
        for (int i = 0; i < dataList.size(); i++) {
            // 创建第二行
            XSSFRow row2 = sheet.createRow(i);
            List<String> rowData = dataList.get(i);
            for (int j = 0; j < rowData.size(); j++) {
                // 创建一个单元格
                XSSFCell cell = row2.createCell(j);
                if (null == rowData.get(j)) {
                    cell.setCellValue("");
                } else {
                    cell.setCellValue(rowData.get(j));
                }
            }
        }
        logger.info("写入数据行完成");
        FileOutputStream fileOutputStream = null;
        try {
            // 创建文件
            fileOutputStream = new FileOutputStream(excelFile);
            // 写入文件
            workbook.write(fileOutputStream);
        } catch (IOException e) {
            logger.error("Error occurred: ", e);
        } finally {
            try {
                if (null != fileOutputStream) {
                    fileOutputStream.close();
                }
            } catch (IOException e) {
                logger.error("Error occurred: ", e);
            }
        }
        long end = System.currentTimeMillis();
        logger.info("写入文件完成，总耗时：{} 秒", (end - start) / 1000.0);
    }

    /**
     * 同一个Excel文件写入多个Sheet
     * 每个工作表由不同的线程生成
     *
     * @param excelFile     Excel文件
     * @param sheetDataList Sheet数据
     */
    @Override
    public void writeMultipleSheet(File excelFile, List<SheetData> sheetDataList) {
        long start = System.currentTimeMillis();
        if (!excelFile.exists()) {
            try {
                FileUtils.touch(excelFile);
            } catch (IOException e) {
                logger.error("Error occurred: ", e);
            }
        }
        logger.info("写入文件：{}", excelFile.getAbsolutePath());
        if (!excelFile.getName().toLowerCase().endsWith(".xlsx")) {
            logger.info("只支持 .xlsx 的文件类型写入！");
            return;
        }
        // 创建一个工作簿 03
        // HSSFWorkbook workbook = new HSSFWorkbook();
        // 创建一个工作簿 07
        XSSFWorkbook workbook = new XSSFWorkbook();
        CellStyle cellStyle = workbook.createCellStyle();
        Font font = workbook.createFont();
        //设置字体
        font.setFontName("宋体");
        //设置字号
        font.setFontHeightInPoints((short) 12);
        //加粗
        font.setBold(true);
        //设置字体颜色
        font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
        cellStyle.setFont(font);
        //文本自动换行
        cellStyle.setWrapText(true);
        //左右居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //上下居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        int sheetIndex = 1;
        for (SheetData sheetData : sheetDataList) {
            if (StringUtils.isEmpty(sheetData.getSheetName())) {
                sheetData.setSheetName("Sheet" + sheetIndex);
            }
            sheetIndex++;
            // 创建一个工作表
            XSSFSheet sheet = workbook.createSheet(sheetData.getSheetName());
            List<String> header = sheetData.getHeader();
            // 创建第一行
            XSSFRow row1 = sheet.createRow(0);
            for (int i = 0; i < header.size(); i++) {
                // 创建一个单元格
                XSSFCell cell = row1.createCell(i);
                cell.setCellStyle(cellStyle);
                cell.setCellValue(header.get(i));
            }
            logger.info("写入标题行完成");
            // 调整列宽
            for (int colNum = 0; colNum < header.size(); colNum++) {
                //固定列宽
                sheet.setColumnWidth(colNum, 20 * 256);
            }
            logger.info("设置列宽完成");
            List<Map<String, String>> dataMapList = sheetData.getDataMapList();
            for (int i = 0; i < dataMapList.size(); i++) {
                // 创建第二行
                XSSFRow row2 = sheet.createRow(i + 1);
                Map<String, String> rowDataMap = dataMapList.get(i);
                for (int j = 0; j < header.size(); j++) {
                    // 创建一个单元格
                    XSSFCell cell = row2.createCell(j);
                    if (null == rowDataMap.get(header.get(j))) {
                        cell.setCellValue("");
                    } else {
                        cell.setCellValue(rowDataMap.get(header.get(j)));
                    }
                }
            }
            logger.info("写入数据行完成");
            //第一行开启筛选
            sheet.setAutoFilter(new CellRangeAddress(0, 0, 0, header.size() - 1));
            logger.info("开启标题行筛选完成");
            //这里是只冻结第一行
            sheet.createFreezePane(0, 1, 0, 1);
            logger.info("冻结标题行完成");
        }
        FileOutputStream fileOutputStream = null;
        try {
            // 创建文件
            fileOutputStream = new FileOutputStream(excelFile);
            // 写入文件
            workbook.write(fileOutputStream);
        } catch (IOException e) {
            logger.error("Error occurred: ", e);
        } finally {
            try {
                if (null != fileOutputStream) {
                    fileOutputStream.close();
                }
            } catch (IOException e) {
                logger.error("Error occurred: ", e);
            }
        }
        long end = System.currentTimeMillis();
        logger.info("写入文件完成，总耗时：{} 秒", (end - start) / 1000.0);
    }
}
